package com.yoooya.base.utils;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.Date;
import java.util.List;

/**
 * Excel工具类
 * 
 * @author Sun Yijing<br />
 *         Copyright © Xiamen Yoooya NetWorks Co.,Ltd.
 * @since yoooya software platform v1.0.0
 * @version 1.0.0 <br />
 *          <table border="1" cellpadding="3" cellspacing="0">
 *          <tr>
 *          <th align="left" colspan="3"><b>修改历史</b></th>
 *          </tr>
 *          <tr>
 *          <td>修改日期</td>
 *          <td>修改人</td>
 *          <td>修改描述</td>
 *          </tr>
 *          <tr>
 *          <td>2018-6-20</td>
 *          <td>Sun Yijing</td>
 *          <td>新建</td>
 *          </tr>
 *          </table>
 */
public class ExcelCreateUtil {

    public HSSFWorkbook wb = null;

    public HSSFSheet sheet = null;

    public HSSFDataFormat format = null;

    public HSSFRow hdRow = null;

    public ExcelCreateUtil() {
        wb = new HSSFWorkbook();
    }

    public ExcelCreateUtil(HSSFWorkbook wb) {
        this.wb = wb;
    }

    /**
     * 设置工作表的格式。
     * <p>
     * 详细描述。
     * </p>
     *
     * @param sheetName
     */
    public void createSheet(String sheetName) {
        sheet = wb.createSheet(sheetName);
        format = wb.createDataFormat();
        hdRow = sheet.createRow(0);
        sheet.setDefaultRowHeightInPoints(20);
        sheet.setDefaultColumnWidth(12);
    }

    /**
     * 设置各列单元格宽度。
     * <p>
     * 详细描述。
     * </p>
     *
     * @param eachCellWidth
     * @param high
     */
    @SuppressWarnings("deprecation")
    public void setDefaultCellHighWidthInRange(short[] eachCellWidth, int high) {
        // 假定第一行和第一行所需的单元个已经建立好了，也就是说，在这之前已经调用了DesignXlsHeaderFooter.setXlsHeader
        sheet.setDefaultRowHeightInPoints(high);// 设置默认高
        /* 设置各列单元格宽度 */
        for (int i = 0; i < eachCellWidth.length; i++) {
            sheet.setColumnWidth((short) i, (short) ((eachCellWidth[i]) * 256));
        }
    }

    /**
     * 添加标题及备注信息
     * <p>
     * 详细描述。
     * </p>
     *
     * @param title     标题
     * @param remark    备注
     * @param columnCnt 从第0列开始，跨越的列数
     */
    @SuppressWarnings("deprecation")
    public void addTitle(String title, String remark, int columnCnt) {
        // 第一行显示标题
        HSSFRow row = sheet.createRow(0);
        Region region = new Region((short) 0, (short) (0), (short) 0,
                (short) (columnCnt - 1));
        HSSFCell mulCell = row.createCell((short) (0));
        mulCell.setCellValue(title); // 跨单元格显示的数据
        HSSFCellStyle hdStyle = setTitleStyle();
        mulCell.setCellStyle(hdStyle);
        sheet.addMergedRegion(region);
        // 第二行显示备注，即图表查询条件
        HSSFRow row1 = sheet.createRow(1);
        Region region1 = new Region((short) 1, (short) (0), (short) 1,
                (short) (columnCnt - 1));
        HSSFCell mulCell1 = row1.createCell((short) (0));
        mulCell1.setCellValue(remark); // 跨单元格显示的数据
        sheet.addMergedRegion(region1);
    }

    /**
     * 表头数据。
     * <p>
     * 详细描述。
     * </p>
     *
     * @param rowvalues
     * @param isStyle
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    public void addHeader(List rowvalues, boolean isStyle, int rowFrom,
                          int columnFrom) throws Exception {
        String[] title = new String[rowvalues.size()];
        for (int i = 0; i < rowvalues.size(); i++) {
            title[i] = (String) rowvalues.get(i);
        }
        HSSFRow row = sheet.createRow(rowFrom);
        if (isStyle == true) {
            for (int i = 0; i < title.length; i++, columnFrom++) {
                HSSFCell cell1 = row.createCell(columnFrom);
                HSSFRichTextString value = new HSSFRichTextString(title[i]);
                cell1.setCellValue(value);
                HSSFCellStyle hdStyle = setHeaderStyle();
                cell1.setCellStyle(hdStyle);
            }
        } else {
            for (int i = 0; i < title.length; i++, columnFrom++) {
                HSSFCell cell2 = row.createCell(columnFrom);
                HSSFRichTextString value2 = new HSSFRichTextString(title[i]);
                cell2.setCellValue(value2);
            }
        }
    }

    /**
     * 表头数据。
     * <p>
     * 详细描述。。。合并标题
     * </p>
     *
     * @param rowvalues 行的起始数
     * @param colvalues 列的起始数
     * @param rowTo     行的合并列的索引
     * @param columnTo  列的和并列的索引
     * @param isStyle
     * @throws Exception
     */
    @SuppressWarnings({"unchecked", "deprecation"})
    public void addHeaderTitle(List rowvalues, boolean isStyle, int rowFrom,
                               int columnFrom, int rowTo, int columnTo) throws Exception {
        String[] title = new String[rowvalues.size()];
        for (int i = 0; i < rowvalues.size(); i++) {
            title[i] = (String) rowvalues.get(i);
        }
        HSSFRow row = sheet.getRow(rowFrom);
        if (null == row) { // 判断是否行已创建
            row = sheet.createRow(rowFrom);
        }
        if (isStyle == true) {
            for (int i = 0; i < title.length; i++, columnFrom++) {
                int column = columnFrom + columnTo;
                Region region = new Region((short) rowFrom,
                        (short) (columnFrom), (short) rowTo, (short) column);
                HSSFCell cell1 = row.createCell(columnFrom);
                columnFrom = column;
                HSSFRichTextString value2 = new HSSFRichTextString(title[i]);
                cell1.setCellValue(value2);
                sheet.addMergedRegion(region);
                HSSFCellStyle hdStyle = setHeaderStyle();
                cell1.setCellStyle(hdStyle);
            }
        } else {
            for (int i = 0; i < title.length; i++, columnFrom++) {
                int column = columnFrom + columnTo;
                Region region = new Region((short) rowFrom,
                        (short) (columnFrom), (short) rowTo, (short) column);
                HSSFCell cell2 = row.createCell(columnFrom);
                columnFrom = column;
                HSSFRichTextString value2 = new HSSFRichTextString(title[i]);
                cell2.setCellValue(value2);
                sheet.addMergedRegion(region);
            }
        }
    }

    /**
     * 设置表头样式
     * <p>
     * 详细描述。
     * </p>
     *
     * @return
     */
    private HSSFCellStyle setHeaderStyle() {
        // 设置字体
        HSSFFont workFont = wb.createFont();
        workFont.setFontName("微软雅黑");
        workFont.setFontHeightInPoints((short) 12);
        workFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 表头样式及背景色
        HSSFCellStyle hdStyle = wb.createCellStyle();
        hdStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        hdStyle.setBottomBorderColor(HSSFColor.BLACK.index);
        hdStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        hdStyle.setLeftBorderColor(HSSFColor.BLACK.index);
        hdStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        hdStyle.setRightBorderColor(HSSFColor.BLACK.index);
        hdStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        hdStyle.setTopBorderColor(HSSFColor.BLACK.index);
        hdStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        hdStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        hdStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        hdStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        hdStyle.setFont(workFont);

        return hdStyle;
    }

    /**
     * 设置标题样式
     * <p>
     * 详细描述。
     * </p>
     *
     * @return
     */
    private HSSFCellStyle setTitleStyle() {
        // 设置字体
        HSSFFont workFont = wb.createFont();
        workFont.setFontName("微软雅黑");
        workFont.setFontHeightInPoints((short) 16);
        workFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        HSSFCellStyle hdStyle = wb.createCellStyle();
        hdStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        hdStyle.setFont(workFont);

        return hdStyle;
    }

    /**
     * 添加数据
     * <p>
     * 详细描述。
     * </p>
     * 区间 Y轴 X轴 0<x<=2 2 2<x<=4 4
     *
     * @param list       数据列表
     * @param rowFrom    合并单元格起始行
     * @param columnFrom 合并单元格起始列
     * @param rowTo      合并单元格结束行，若为-1，则不添加合并单元格
     * @param columnTo   合并单元格结束列，若为-1，则不添加合并单元格
     * @param title      合并单元格标题
     */
    @SuppressWarnings({"unchecked", "deprecation"})
    public void addData(List list, int rowFrom, int columnFrom, int rowTo,
                        int columnTo, String title) {
        if (list != null && list.size() > 0) {
            int columnFromOrigin = columnFrom;
            HSSFCellStyle dtStyle = wb.createCellStyle();
            dtStyle.setDataFormat(format.getFormat("text"));
            dtStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            dtStyle.setBottomBorderColor(HSSFColor.BLACK.index);
            dtStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            dtStyle.setLeftBorderColor(HSSFColor.BLACK.index);
            dtStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            dtStyle.setRightBorderColor(HSSFColor.BLACK.index);
            dtStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            dtStyle.setTopBorderColor(HSSFColor.BLACK.index);
            dtStyle.setWrapText(true);
            dtStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
            HSSFCellStyle dateStyle = wb.createCellStyle();
            dateStyle.setDataFormat(format.getFormat("yyyy-m-d"));
            dateStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            dateStyle.setBottomBorderColor(HSSFColor.BLACK.index);
            dateStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            dateStyle.setLeftBorderColor(HSSFColor.BLACK.index);
            dateStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            dateStyle.setRightBorderColor(HSSFColor.BLACK.index);
            dateStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            dateStyle.setTopBorderColor(HSSFColor.BLACK.index);
            dateStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
            dateStyle.setWrapText(true);
            for (int k = 0; k < list.size(); k++, rowFrom++) {
                if (rowTo != -1 && columnTo != -1) {
                    columnFrom = columnFromOrigin + 1;// 若有合并单元格，则数据列从下一列开始
                } else {
                    columnFrom = columnFromOrigin;
                }
                List rowvalues = (List) list.get(k);
                HSSFRow dtRow = sheet.createRow(rowFrom);
                if (rowTo != -1 && columnTo != -1 && k == 0) {
                    Region region = new Region((short) rowFrom,
                            (short) (columnFromOrigin), (short) rowTo,
                            (short) columnTo);
                    HSSFCell MulCell = dtRow
                            .createCell((short) (columnFromOrigin));
                    MulCell.setCellValue(title); // 跨单元格显示的数据
                    HSSFCellStyle hdStyle = setHeaderStyle();
                    MulCell.setCellStyle(hdStyle);
                    sheet.addMergedRegion(region);
                }
                if (rowvalues != null && rowvalues.size() > 0) {
                    for (int j = 0; j < rowvalues.size(); j++, columnFrom++) {
                        String flag = "";
                        Object cell_data = rowvalues.get(j);
                        HSSFCell cell = dtRow.createCell(columnFrom);
                        // 正文格式
                        if (cell_data instanceof String) {
                            flag = "string";
                            cell.setCellValue((String) cell_data);
                        } else if (cell_data instanceof Double) {
                            cell.setCellValue((Double) cell_data);
                        } else if (cell_data instanceof Integer) {
                            cell.setCellValue(Double.valueOf(String
                                    .valueOf(cell_data)));
                        } else if (cell_data instanceof Long) {
                            cell.setCellValue(Double.valueOf(String
                                    .valueOf(cell_data)));
                        } else if (cell_data instanceof Date) {
                            flag = "date";
                            cell.setCellValue((Date) cell_data);
                        } else if (cell_data instanceof Boolean) {
                            cell.setCellValue((Boolean) cell_data);
                        } else if (cell_data instanceof Float) {
                            cell.setCellValue((Float) cell_data);
                        }
                        if (flag == "" || flag.equals("string")) {
                            cell.setCellStyle(dtStyle);
                        } else if (flag.equals("date")) {
                            cell.setCellStyle(dateStyle);
                        }
                    }
                }
            }
        }

    }

    /**
     * 具体文件生成的路径
     *
     * @param file
     * @throws Exception
     */
    public void exportExcel(String file) throws Exception {
        FileOutputStream fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
    }

    /**
     * 具体文件生成的文件
     *
     * @param file
     * @throws Exception
     */
    public void exportExcel(File file) throws Exception {
        FileOutputStream fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
    }

    /**
     * 具体文件生成的文件
     *
     * @param file
     * @throws Exception
     */
    public void exportExcel(OutputStream outputstream) throws Exception {
        BufferedOutputStream buffout = new BufferedOutputStream(outputstream);
        wb.write(buffout);
        buffout.flush();
        buffout.close();
    }

}
